package controller;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashSet;
import java.util.Set;

import model.Adresse;
import model.Kunde;
import model.ManagementAssistent;
import model.Ort;
import model.Produkt;
import model.Trainer;
import model.Training;

/**
 * DatabaseController
 * 
 * @author ldonker
 * @version 0.1
 * 
 */

public class DatabaseController implements IDatabaseController {
	/**
	 * Attribute
	 */
	Connection c = null;
	boolean isConnected = false;
	String url = "jdbc:mysql://localhost:3306/plentra";
	String driver = "com.mysql.jdbc.Driver";
	
	ManagementAssistent managementMitarbeiter;

	public DatabaseController() {
		this.managementMitarbeiter = new ManagementAssistent(1, "Test", "Admin", "Max");
	}
	
	public DatabaseController(ManagementAssistent managementMitarbeiter) {
		this.managementMitarbeiter = managementMitarbeiter;
	}

	/**
	 * Methoden-Bock aus Interface
	 * 
	 */

	@Override
	public boolean connect() {
		if (isConnected == false) {
			try {
				Class.forName(driver).newInstance();
				c = DriverManager.getConnection(url, "root", "");

				isConnected = true;
//				System.out.println("Connected to Database");
				return true;
			}

			catch (Exception e) {
				System.out.println(e.getMessage());
				isConnected = false;
				return false;
			}
		} else {
			isConnected = true;
			return true;
		}

	}

	@Override
	public Adresse getAdresseByID(int adressID) {
		Adresse adress = new Adresse();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return adress;
		}
		try {
			String query = "select strasse, bundesland, stadt, postleitzahl, art "
					+ "from adresse " + "where adressID = ?;";
			PreparedStatement prestmt = c.prepareStatement(query);
			prestmt.setInt(1, adressID);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				adress.setAdressID(adressID);
				adress.setArt(rs.getString("art"));
				adress.setBundesland(rs.getString("bundesland"));
				adress.setPostleitzahl(rs.getInt("postleitzahl"));
				adress.setStadt(rs.getString("stadt"));
				adress.setStrasse(rs.getString("strasse"));
			}
		} catch (Exception e) {
			System.out.println("Adresse" + adressID + "not found in Database");
			e.printStackTrace();
		}

		return adress;
	}

	@Override
	public Kunde getKundeByID(int kundenID) {
		Kunde kunde = new Kunde();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return kunde;
		}
		try {
			String query = "select firmenname, branche, name, vorname, email, "
					+ "telefon, bemerkungen, adressID, mitarbeiterID "
					+ "from kunde " + "where kundenId = ?;";
			PreparedStatement prestmt = c.prepareStatement(query);
			prestmt.setInt(1, kundenID);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				kunde.setKundenID(kundenID);
				kunde.setFirmenname(rs.getString("firmenname"));
				kunde.setBranche(rs.getString("branche"));
				kunde.setName(rs.getString("name"));
				kunde.setVorname(rs.getString("vorname"));
				kunde.setEmail(rs.getString("email"));
				kunde.setTelefon(rs.getString("telefon"));
				kunde.setBemerkungen(rs.getString("bemerkungen"));
				kunde.setAdresse(this.getAdresseByID(rs.getInt("adressID")));
			}
		} catch (Exception e) {
			System.out.println("Kunde " + kundenID + "not found in Database");
			e.printStackTrace();
		}
		return kunde;
	}

	@Override
	public ManagementAssistent getManagementassistentByID(int mitarbeiterID) {
		ManagementAssistent managementassistent = new ManagementAssistent();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return managementassistent;
		}
		try {
			String query = "select name, vorname, rolle "
					+ "from managementassistent " + "where mitarbeiterID = ?;";
			PreparedStatement prestmt = c.prepareStatement(query);
			prestmt.setInt(1, mitarbeiterID);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				managementassistent.setMitarbeiterID(mitarbeiterID);
				managementassistent.setName(rs.getString("name"));
				managementassistent.setVorname(rs.getString("vorname"));
				managementassistent.setRolle(rs.getString("rolle"));
			}
		} catch (Exception e) {
			System.out.println("Mitarbeiter" + mitarbeiterID + "not found in Database");
			e.printStackTrace();
		}
		return managementassistent;
	}

	@Override
	public Ort getOrtByID(int ortsID) {
		Ort ort = new Ort();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return ort;
		}
		try {
			String query = "select gebaeude, raum " + "from ort "
					+ "where ortsID = ?;";
			PreparedStatement prestmt = c.prepareStatement(query);
			prestmt.setInt(1, ortsID);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				ort.setOrtsID(ortsID);
				ort.setRaum(rs.getString("raum"));
				ort.setGebaeude(rs.getString("gebaeude"));
			}
		} catch (Exception e) {
			System.out.println("Ort " + ortsID + "not found in Database");
			e.printStackTrace();
		}
		return ort;
	}

	@Override
	public Produkt getProduktByID(int produktID) {
		Produkt produkt = new Produkt();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return produkt;
		}
		try {
			String query = "select bezeichnung, beschreibung, bemerkung, version "
					+ "from produkt " + "where produktID = ?;";
			PreparedStatement prestmt = c.prepareStatement(query);
			prestmt.setInt(1, produktID);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				produkt.setProduktID(produktID);
				produkt.setBezeichnung(rs.getString("bezeichnung"));
				produkt.setBeschreibung(rs.getString("beschreibung"));
				produkt.setBemerkungen(rs.getString("bemerkung"));
				produkt.setVersion(rs.getString("version"));
			}
		} catch (Exception e) {
			System.out.println("Produkt" + produktID + "not found in Database");
			e.printStackTrace();
		}
		return produkt;
	}

	@Override
	public Trainer getTrainerByID(int trainerID) {
		Trainer trainer = new Trainer();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return trainer;
		}
		try {
			String query = "select vorname, name " + "from trainer "
					+ "where trainerID = ?;";
			PreparedStatement prestmt = c.prepareStatement(query);
			prestmt.setInt(1, trainerID);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				trainer.setTrainerID(trainerID);
				trainer.setVorname(rs.getString("vorname"));
				trainer.setName(rs.getString("name"));
			}
		} catch (Exception e) {
			System.out.println("trainer" + trainerID + "not found in Database");
			e.printStackTrace();
		}
		return trainer;
	}

	@Override
	public Training getTrainingByID(int trainingsID) {
		Training training = new Training();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return training;
		}
		try {
			String query = "select tage, anfangsdatum, enddatum, "
					+ "kundenID, ortsID, produktID, trainerID "
					+ "from training " + "where trainingsID = ?;";
			PreparedStatement prestmt = c.prepareStatement(query);
			prestmt.setInt(1, trainingsID);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				training.setTrainingsID(trainingsID);
				training.setTage(rs.getInt("tage"));
				training.setAnfangsdatum(rs.getDate("anfangsdatum").toString());
				training.setEnddatum(rs.getDate("enddatum").toString());
				training.setManagementassistent(this
						.getLastManagementAssistentByTrainingID(trainingsID));
				training.setKunde(this.getKundeByID(rs.getInt("kundenID")));
				training.setOrt(this.getOrtByID(rs.getInt("ortsID")));
				training.setProdukt(this.getProduktByID(rs.getInt("produktID")));
				training.setTrainer(this.getTrainerByID(rs.getInt("trainerID")));
			}
		} catch (Exception e) {
			System.out.println("Training" + trainingsID + "not found in Database");
			e.printStackTrace();
		}

		return training;
	}

	@Override
	public boolean close() {
		if (isConnected == true) {
			try {
				c.close();
				isConnected = false;
//				System.out.println("Disconnected to Database");
				return false;
			} catch (Exception e) {
				System.out.println(e.getMessage());
				isConnected = true;
				return true;
			}
		} else {
			isConnected = false;
			return false;
		}

	}

	@Override
	public ManagementAssistent getLastManagementAssistentByTrainingID(
			int trainingID) {
		ManagementAssistent mitarbeiter = new ManagementAssistent();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return mitarbeiter;
		}
		try {
			
			String query = "SELECT managementassistent.mitarbeiterID, name, vorname, rolle " +
							"FROM ( SELECT mitarbeiterID, trainingsID, MAX( aenderungsDatum ) " +
							"FROM managementassistent__training) b " +
							"INNER JOIN managementassistent " +
							"ON b.mitarbeiterID = managementassistent.mitarbeiterID " +
							"WHERE trainingsID =?;";
			PreparedStatement prestmt = c.prepareStatement(query);
			prestmt.setInt(1, trainingID);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				mitarbeiter.setMitarbeiterID(rs.getInt("mitarbeiterID"));
				mitarbeiter.setName(rs.getString("name"));
				mitarbeiter.setVorname(rs.getString("vorname"));
				mitarbeiter.setRolle(rs.getString("rolle"));
			}
		} catch (Exception e) {
			System.out.println("NO ManagementAssistent for " + trainingID
					+ "found in Database");
			e.printStackTrace();
		}

		return mitarbeiter;
	}

	@Override
	public Set<Ort> getOrtList() {
		Set<Ort> orte = new HashSet<Ort>();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return orte;
		}
		try {
			String query = "select ortsID, gebaeude, raum " + "from ort order by ortsID;";
			PreparedStatement prestmt = c.prepareStatement(query);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				Ort ort = new Ort();
				ort.setOrtsID(rs.getInt("ortsID"));
				ort.setRaum(rs.getString("raum"));
				ort.setGebaeude(rs.getString("gebaeude"));
				orte.add(ort);
			}
		} catch (Exception e) {
			System.out.println("NO 'Ort' found in Database");
			e.printStackTrace();
		}

		return orte;
	}

	@Override
	public Set<Produkt> getProduktList() {
		Set<Produkt> produkte = new HashSet<Produkt>();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return produkte;
		}
		try {
			String query = "select produktID, bezeichnung, beschreibung, bemerkung, version "
					+ "from produkt order by produktID;";
			PreparedStatement prestmt = c.prepareStatement(query);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				Produkt produkt = new Produkt();
				produkt.setProduktID(rs.getInt("produktID"));
				produkt.setBezeichnung(rs.getString("bezeichnung"));
				produkt.setBeschreibung(rs.getString("beschreibung"));
				produkt.setBemerkungen(rs.getString("bemerkung"));
				produkt.setVersion(rs.getString("version"));
				produkte.add(produkt);
			}
		} catch (Exception e) {
			System.out.println("NO 'Produkt' found in Database");
			e.printStackTrace();
		}
		return produkte;
	}

	@Override
	public Set<Trainer> getTrainerbyProdukt(int produktID) {
		Set<Trainer> trainerz = new HashSet<Trainer>();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return trainerz;
		}
		try {
			String query = "select trainerID, vorname, name "
					+ "from trainer inner join trainerprofil "
					+ "on trainer.trainerID = trainerprofil.trainerID "
					+ "where produktID = ? order by trainerID;";
			PreparedStatement prestmt = c.prepareStatement(query);
			prestmt.setInt(1, produktID);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				Trainer trainer = new Trainer();
				trainer.setTrainerID(rs.getInt("trainerID"));
				trainer.setVorname(rs.getString("vorname"));
				trainer.setName(rs.getString("name"));
				trainerz.add(trainer);
			}
		} catch (Exception e) {
			System.out.println("NO Trainer for Produkt " + produktID
					+ " found in Database");
			e.printStackTrace();
		}
		return trainerz;
	}

	@Override
	public boolean insertProdukt(Produkt produkt) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String insert = "INSERT INTO produkt (bezeichnung, beschreibung, bemerkung, version) "
				+ "VALUES (?, ?, ?, ?)";
		try {
			PreparedStatement prestmt = c.prepareStatement(insert);

			prestmt.setString(1, produkt.getBezeichnung());
			prestmt.setString(2, produkt.getBeschreibung());
			prestmt.setString(3, produkt.getBemerkungen());
			prestmt.setString(4, produkt.getVersion());

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("INSERT Produkt: " + produkt.getProduktID()
					+ " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean insertProduktProfile(int trainerID, int produktID) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String insert = "INSERT INTO trainerprofil " + "VALUES (?, ?)";
		try {
			PreparedStatement prestmt = c.prepareStatement(insert);

			prestmt.setInt(1, trainerID);
			prestmt.setInt(2, produktID);

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("INSERT Produktprofile: " + trainerID + "/"
					+ produktID + " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean insertTrainer(Trainer trainer) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String insert = "INSERT INTO trainer (vorname, name) "
				+ "VALUES (?, ?)";
		try {
			PreparedStatement prestmt = c.prepareStatement(insert);

			prestmt.setString(1, trainer.getVorname());
			prestmt.setString(2, trainer.getName());

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("INSERT Trainer: " + trainer.getTrainerID()
					+ " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean insertOrt(Ort ort) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String insert = "INSERT INTO ort (gebaeude, raum) " + "VALUES (?, ?)";
		try {
			PreparedStatement prestmt = c.prepareStatement(insert);

			prestmt.setString(1, ort.getGebaeude());
			prestmt.setString(2, ort.getRaum());

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("INSERT Ort: " + ort.getOrtsID()
					+ " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean insertTraining(Training training) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String insert = "INSERT INTO training (anfangsdatum, enddatum, tage, kundenID, ortsID, produktID, trainerID) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?)";
		try {

			java.util.Date utilDateAnfang = training.getAnfangsdatum();
			java.sql.Date sqlDateAnfang = new java.sql.Date(
					utilDateAnfang.getTime());

			java.util.Date utilDateEnd = training.getEnddatum();
			java.sql.Date sqlDateEnd = new java.sql.Date(utilDateEnd.getTime());

			PreparedStatement prestmt = c.prepareStatement(insert);
			prestmt.setDate(1, sqlDateAnfang);
			prestmt.setDate(2, sqlDateEnd);
			prestmt.setInt(3, training.getTage());
			prestmt.setInt(4, training.getKunde().getKundenID());
			prestmt.setInt(5, training.getOrt().getOrtsID());
			prestmt.setInt(6, training.getProdukt().getProduktID());
			prestmt.setInt(7, training.getTrainer().getTrainerID());

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("INSERT Training: " + training.getTrainingsID()
					+ " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean insertKunde(Kunde kunde) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String insert = "INSERT INTO kunde (firmenname, branche, name, vorname, email, telefon, bemerkungen, adressID, mitarbeiterID) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			PreparedStatement prestmt = c.prepareStatement(insert);

			prestmt.setString(1, kunde.getFirmenname());
			prestmt.setString(2, kunde.getBranche());
			prestmt.setString(3, kunde.getName());
			prestmt.setString(4, kunde.getVorname());
			prestmt.setString(5, kunde.getEmail());
			prestmt.setString(6, kunde.getTelefon());
			prestmt.setString(7, kunde.getBemerkungen());
			prestmt.setInt(8, kunde.getAdresse().getAdressID());
			prestmt.setInt(9, managementMitarbeiter.getMitarbeiterID());

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("INSERT Kunde: " + kunde.getKundenID()
					+ " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean insertAdresse(Adresse adresse) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String insert = "INSERT INTO adresse (strasse, bundesland, stadt, postleitzahl, art) "
				+ "VALUES (?, ?, ?, ?, ?)";
		try {
			PreparedStatement prestmt = c.prepareStatement(insert);

			prestmt.setString(1, adresse.getStrasse());
			prestmt.setString(2, adresse.getBundesland());
			prestmt.setString(3, adresse.getStadt());
			prestmt.setInt(4, adresse.getPostleitzahl());
			prestmt.setString(5, adresse.getArt());

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("INSERT Adresse: " + adresse.getAdressID()
					+ " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean insertManagementAssistent(ManagementAssistent mitarbeiter) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String insert = "INSERT INTO managementassistent (name, vorname, rolle) "
				+ "VALUES (?, ?, ?)";
		try {
			PreparedStatement prestmt = c.prepareStatement(insert);

			prestmt.setString(1, mitarbeiter.getName());
			prestmt.setString(2, mitarbeiter.getVorname());
			prestmt.setString(3, mitarbeiter.getRolle());

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("INSERT ManagementAssistent: "
					+ mitarbeiter.getMitarbeiterID() + " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean insertManagementAssistentTOTraining(int mitarbeiterID,
			int trainingID, String bemerkung) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String insert = "INSERT INTO managementassistent__training (mitarbeiterID, trainingsID, bemerkung) "
				+ "VALUES (?, ?, ?)";
		try {
			PreparedStatement prestmt = c.prepareStatement(insert);

			prestmt.setInt(1, mitarbeiterID);
			prestmt.setInt(2, trainingID);
			prestmt.setString(3, bemerkung);

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("INSERT ManagementAssistent__Training: "
					+ mitarbeiterID + ";" + trainingID + " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean updateTraining(Training training) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String update = "UPDATE training "
				+ "SET anfangsdatum = ?, enddatum = ?, tage = ?, "
				+ "kundenID = ?, ortsID = ?, produktID = ?, trainerID = ? "
				+ "WHERE trainingsID = ?";
		try {
			PreparedStatement prestmt = c.prepareStatement(update);

			prestmt.setDate(1, (Date) training.getAnfangsdatum());
			prestmt.setDate(2, (Date) training.getEnddatum());
			prestmt.setInt(3, training.getTage());
			prestmt.setInt(4, training.getKunde().getKundenID());
			prestmt.setInt(5, training.getOrt().getOrtsID());
			prestmt.setInt(6, training.getProdukt().getProduktID());
			prestmt.setInt(7, training.getTrainer().getTrainerID());

			prestmt.setInt(8, training.getTrainingsID());

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("UPDATE Training: " + training.getTrainingsID()
					+ " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean updateKunde(Kunde kunde) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String update = "UPDATE kunde "
				+ "SET firmenname = ?, branche = ?, name = ?, vorname = ?, "
				+ "email = ?, telefon = ?, bemerkungen = ? "
				+ "adressID = ?, mitarbeiterID = ? " + "WHERE kundenID = ?";
		try {
			PreparedStatement prestmt = c.prepareStatement(update);

			prestmt.setString(1, kunde.getFirmenname());
			prestmt.setString(2, kunde.getBranche());
			prestmt.setString(3, kunde.getName());
			prestmt.setString(4, kunde.getVorname());
			prestmt.setString(5, kunde.getEmail());
			prestmt.setString(6, kunde.getTelefon());
			prestmt.setString(7, kunde.getBemerkungen());
			prestmt.setInt(8, kunde.getAdresse().getAdressID());
			prestmt.setInt(9, managementMitarbeiter.getMitarbeiterID());

			prestmt.setInt(10, kunde.getKundenID());

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("UPDATE Kunde: " + kunde.getKundenID()
					+ " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public boolean deleteTraining(int trainingID) {
		Boolean isSuccessfull = false;
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return isSuccessfull;
		}
		String update = "DELETE " + "FROM training" + "WHERE trainingsID = ?";
		try {
			PreparedStatement prestmt = c.prepareStatement(update);

			prestmt.setInt(1, trainingID);

			prestmt.execute();
			isSuccessfull = true;
		} catch (Exception e) {
			System.out.println("DELETE Training: " + trainingID
					+ " not successfull!");
			e.printStackTrace();
			isSuccessfull = false;
		}
		return isSuccessfull;
	}

	@Override
	public Set<Training> getTrainingList() {
		Set<Training> produkte = new HashSet<Training>();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return produkte;
		}
		try {
			String query = "select trainingsID, tage, anfangsdatum, enddatum, "
					+ "kundenID, ortsID, produktID, trainerID "
					+ "from training order by trainingsID;";
			PreparedStatement prestmt = c.prepareStatement(query);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				Training training = new Training();
				training.setTrainingsID(rs.getInt("trainingsID"));
				training.setTage(rs.getInt("tage"));
				training.setAnfangsdatum(rs.getDate("anfangsdatum").toString());
				training.setEnddatum(rs.getDate("enddatum").toString());
				training.setKunde(getKundeByID(rs.getInt("kundenID")));
				training.setOrt(getOrtByID(rs.getInt("ortsID")));
				training.setProdukt(getProduktByID(rs.getInt("produktID")));
				training.setTrainer(getTrainerByID(rs.getInt("trainerID")));
				produkte.add(training);
			}
		} catch (Exception e) {
			System.out.println("NO 'Ort' found in Database");
			e.printStackTrace();
		}

		return produkte;
	}

	@Override
	public Set<Trainer> getTrainerList() {
		Set<Trainer> trainerz = new HashSet<Trainer>();
		if (isConnected == false) {
			System.out.print("Not Connected to Database!");
			return trainerz;
		}
		try {
			String query = "select trainerID, vorname, name "
					+ "from trainer order by trainerID;";
			PreparedStatement prestmt = c.prepareStatement(query);
			ResultSet rs = prestmt.executeQuery();
			while (rs.next()) {
				Trainer trainer = new Trainer();
				trainer.setTrainerID(rs.getInt("trainerID"));
				trainer.setVorname(rs.getString("vorname"));
				trainer.setName(rs.getString("name"));
				trainerz.add(trainer);
			}
		} catch (Exception e) {
			System.out.println("NO Trainer found in Database");
			e.printStackTrace();
		}
		return trainerz;
	}
}
